<?php
  class sql_functions{
               public function select_query($demand,$dbase=NULL){
                   #this function gets a query string and return the query result
                   #@("the query string","cashier")
                   $sql_str = "select $demand";
                 
                   if($dbase==NULL){
                       $connection=new connection("registrar");
                       $sql_query = mysqli_query($connection->open(),$sql_str) or die("error sa dbase".mysqli_error($connection->open())); 
                   }elseif($dbase=="cashier"){
                       $connection=new connection("cashier");
                       $sql_query = mysqli_query($connection->open(),$sql_str) or die("error sa dbase".mysqli_error($connection->open())); 
                   }elseif($dbase=="information_schema"){
                       $connection=new connection("information_schema");
                       $sql_query = mysqli_query($connection->open(),$sql_str) or die("error sa dbase".mysqli_error($connection->open())); 
                   }                                       
                    return $sql_query;
              }
              public function fetch_to_array($query){
                  #this function gets a query result and return the result rows as array
                  #@("this valid query")
                  $result = array(); 
                  while($row=mysqli_fetch_assoc($query)){
                        array_push($result,$row);
                   }
                   return $result;                    
              }
              public function count_rows($query,$dbase=NULL){
                  #this function gets a query result and return the count of rows
                  #@("this valid query")
                  $count = mysqli_num_rows($query);
                  return $count;
              }
              public function apply_query($query,$dbase=NULL){
                  if($dbase==NULL){
                     $connection=new connection("registrar");
                     mysqli_query($connection->open(),$query) or die("error sa dbase".mysqli_error($connection->open()));
                  }else{
                    $connection=new connection("cashier");
                    mysqli_query($connection->open(),$query) or die("error sa dbase".mysqli_error($connection->open()));   
                  }
              }    
} 
class download_excel{
      
      function __construct($filename){
           $this->namefile = $filename;
            #excel beginning declaration
            #start excel
            error_reporting(E_ALL);
            date_default_timezone_set('Asia/Manila');    
            require_once '../Classes/PHPExcel.php';
            $objReader = new PHPExcel_Reader_Excel5();
            $this->objPHPExcel= $objReader->load("".$this->namefile);  
            ini_set("memory_limit","-1");
      ##end of excel declarationi
      }
     
      function unload_excel(){
        #excel ending declaration
         #close excel        
             $this->objPHPExcel->disconnectWorksheets();                
             unset($this->objPHPExcel);       
        ##end of excel declaration
      } 
      
      function  set_cell_value($cell,$value){
         #insert directly to excel cell
         #@("b10","testing"); 
          $this->objPHPExcel->getActiveSheet()->setCellValue($cell,$value);
      }
      
      function set_column_row_value($column,$row,$value){
          #this inserts values to the cell by column and row
          #@("0","2","ako");
           $this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($column,$row,$value);  
       
      }
      
      function protect_sheet($password){
          #this protects the active sheet with a password
          #@("relan");
          $this->objPHPExcel->getActiveSheet()->getProtection()->setPassword($password);
          $this->objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
      }
      
      function hide_cell($row){
          #this hides a particular row
          #@("10");
          $this->objPHPExcel->getActiveSheet()->getRowDimension($row)->setVisible(false);
      }
      
      function save_to_server($folder_filename){
          #this will save the excel file to server folder
          #@("extra/relan.xls")
                $objWriter = new PHPExcel_Writer_Excel5($this->objPHPExcel);
                $objWriter->save($folder_filename);
      }
      
      function force_download($filename){ 
        #this force dowload the excel file to the browser
        #@("Relan") 
        #clean the headrs sent and force the output   
          if (ob_get_contents()!='') {
                ob_clean();
           }   
             header('Content-Type: application/vnd.ms-excel');
             header('Content-Disposition: attachment;filename="'.$filename.'.xls');
             header('Cache-Control: max-age=0');
               
              $objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
              $objWriter->save('php://output');
              ob_end_flush();
        
      }  
      
      function get_value_cell($column,$row){
          #this function grabs the cell value of an excel file
          $value = $this->objPHPExcel->getActiveSheet()->getCellByColumnAndRow($column, $row)->getValue();
          return  $value;
           }
            
  }
  class security{
      
      function record_logs($username,$function_name,$description,$ginagawa){
          $query = sql_functions::select_query("* from logs_event where username='".$username."' and adlaw=curdate() order by id desc limit 1");
          if(sql_functions::count_rows($query)==0){
              sql_functions::apply_query("insert into logs_event(username,ginagawa,adlaw)values('".$username."','".$function_name."-".$description."-".$ginagawa."-".date("h:i a")."|','".date('Y-m-d')."')");
          }else{
               sql_functions::apply_query("update logs_event set ginagawa=concat(ginagawa,'".$function_name."-".$description."-".$ginagawa."-".date("h:i a")."','|') where username='".$username."' and adlaw='".date('Y-m-d')."' order by id desc limit 1");       
          }
              
      }
      
      function head_entry(){
            if(security::check_ip_status($_SERVER['REMOTE_ADDR'])){
                 $blocked=true;
            }else{
      
                if(security::check_if_allowed($_SERVER['REMOTE_ADDR'])){
                    $blocked=true;
                 }else{           
                    $blocked=false;
                 }
            }
            return $blocked;      
      }
      
      
      function check_ip_status($ip){
          $query = sql_functions::select_query("* from  ip_list where ip_no='".$ip."'");
          //$query = sql_functions::select_query("ip_no,SUBTIME(time(now()),time(oras)) as deperence FROM ip_list where ip_no='".$ip."' having deperence>time('00:05:00')");
          if(sql_functions::count_rows($query)==0){
              #insert the ip no
              sql_functions::apply_query("insert into ip_list(ip_no,no_attempt,oras)values('".$ip."',0,now())");
              return true;
          }else{
              $status = sql_functions::fetch_to_array($query);
              if($status[0]['no_attempt']==3){
                  return false;
              }else{
                  return true;
              }
          }    
      }
      
      function check_if_allowed($ip){
          $query =  sql_functions::select_query("ip_no,SUBTIME(time(now()),time(oras)) as deperence FROM ip_list where ip_no='".$ip."' having deperence>time('00:01:00')");
          if(sql_functions::count_rows($query)==1){
              return true;
          }else{
              return false;
          }
          
      }
      
      function increment_failed_ip($ip){
          ##########
         security::record_logs($_SESSION['username'],"increment_failed_ip","update the ip_list table for failed attempt","Update failed Attempt"); 
          ##########
               sql_functions::apply_query("update ip_list set no_attempt=if(no_attempt +1>=6,5,no_attempt+1),oras=now() where ip_no='".$ip."'");
      }
   
      function reset_ip_counter($ip){
          ##########
          security::record_logs($_SESSION['username'],"reset_ip_counter","reset the ip for valid credentials","Resetting the ip to zero");
          #########
            sql_functions::apply_query("update ip_list set no_attempt=0 where ip_no='".$ip."'");
      } 
         
               
  }
  
 class miscellaneous{
      function convert_number_to_words($number) {//downloaded function
   
    $hyphen      = '-';
    //$conjunction = ' and ';
    $conjunction = '  ';
    $separator   = ', ';
    $negative    = 'negative ';
    $decimal     = ' point ';
    $dictionary  = array(
        0                   => 'zero',
        1                   => 'one',
        2                   => 'two',
        3                   => 'three',
        4                   => 'four',
        5                   => 'five',
        6                   => 'six',
        7                   => 'seven',
        8                   => 'eight',
        9                   => 'nine',
        10                  => 'ten',
        11                  => 'eleven',
        12                  => 'twelve',
        13                  => 'thirteen',
        14                  => 'fourteen',
        15                  => 'fifteen',
        16                  => 'sixteen',
        17                  => 'seventeen',
        18                  => 'eighteen',
        19                  => 'nineteen',
        20                  => 'twenty',
        30                  => 'thirty',
        40                  => 'fourty',
        50                  => 'fifty',
        60                  => 'sixty',
        70                  => 'seventy',
        80                  => 'eighty',
        90                  => 'ninety',
        100                 => 'hundred',
        1000                => 'thousand',
        1000000             => 'million',
        1000000000          => 'billion',
        1000000000000       => 'trillion',
        1000000000000000    => 'quadrillion',
        1000000000000000000 => 'quintillion'
    );
   
    if (!is_numeric($number)) {
        return false;
    }
   
    if (($number >= 0 && (int) $number < 0) || (int) $number < 0 - PHP_INT_MAX) {
        // overflow
        trigger_error(
            'convert_number_to_words only accepts numbers between -' . PHP_INT_MAX . ' and ' . PHP_INT_MAX,
            E_USER_WARNING
        );
        return false;
    }

    if ($number < 0) {
        return $negative . convert_number_to_words(abs($number));
    }
   
    $string = $fraction = null;
   
    if (strpos($number, '.') !== false) {
        list($number, $fraction) = explode('.', $number);
    }
   
    switch (true) {
        case $number < 21:
            $string = $dictionary[$number];
            break;
        case $number < 100:
            $tens   = ((int) ($number / 10)) * 10;
            $units  = $number % 10;
            $string = $dictionary[$tens];
            if ($units) {
                $string .= $hyphen . $dictionary[$units];
            }
            break;
        case $number < 1000:
            $hundreds  = $number / 100;
            $remainder = $number % 100;
            $string = $dictionary[$hundreds] . ' ' . $dictionary[100];
            if ($remainder) {
                $string .= $conjunction . miscellaneous::convert_number_to_words($remainder);
            }
            break;
        default:
            $baseUnit = pow(1000, floor(log($number, 1000)));
            $numBaseUnits = (int) ($number / $baseUnit);
            $remainder = $number % $baseUnit;
            $string = miscellaneous::convert_number_to_words($numBaseUnits) . ' ' . $dictionary[$baseUnit];
            if ($remainder) {
                $string .= $remainder < 100 ? $conjunction : $separator;
                $string .= miscellaneous::convert_number_to_words($remainder);
            }
            break;
    }
   
    if (null !== $fraction && is_numeric($fraction)) {
        $string .= $decimal;
        $words = array();
        foreach (str_split((string) $fraction) as $number) {
            $words[] = $dictionary[$number];
        }
        $string .= implode(' ', $words);
    }
    return $string;
}

    function buwan(){
      #this function returns array of months  
        $month = array('1'=>'January','2'=>'February','3'=>'March','4'=>'April','5'=>'May','6'=>'June','7'=>'July','8'=>'August','9'=>'September','10'=>'October','11'=>'November','12'=>'December');
        return $month;
    }
    
    function school_year(){
       #this function will return school year 
        $cur_year = date('Y');
        $start = $cur_year - 5;
        $school_year = array();
        for($i = $start;$i<=$cur_year;$i++){
            array_push($school_year,array($i=>$i."-".($i+1)));
        }
        return $school_year;
    }
    
    function load_session($data,$field){       
        #algorithm to get the load of session tble data
        #1. Create an array for storage
            $ibabalik = array();
        #2. prepare the string, covert it to array    
        $ako = explode(";",$data);
        #3. trim unwanted characters
        foreach($ako as $ikaw){
            $siya= str_replace('"','',strchr($ikaw,'"'));
            array_push($ibabalik,$siya);
        }
        switch($field){
            case 'username':
                return $ibabalik[0];
                break;
            case 'password':
                return $ibabalik[1];
                break; 
             case 'profile_id':
                return $ibabalik[2];
                break;    
             case 'syear':
                return $ibabalik[3];
                break;     
             case 'user_type':
                return $ibabalik[4];
                break;     
             case 'name':
                return $ibabalik[5];
                break;   
             case 'page':
                return $ibabalik[6];
                break;     
             case 'page':
                return $ibabalik[7];
                break;     
        }
        #end of algorithm
    }
    
    function view_log_event($text,$part,$limit=NULL){
          $butangan = array(); 
        $explode = explode("|",$text);
        if(count($explode)>=30){
             sql_functions::apply_query("insert into logs_event(username,ginagawa,adlaw)values('".$_SESSION['username']."','','".date('Y-m-d')."')");
             header("location:analytics.php");
        }
        foreach($explode as $explodes){
            $raw = explode("-",$explodes);
                if($part=="ginagawa"){
                    array_push($butangan,$raw[2]."-".$raw[3]);
                }elseif($part=="function_name"){
                    array_push($butangan,$raw[0]."-".$raw[3]);
                }elseif($part=="description"){
                    array_push($butangan,$raw[1]."-".$raw[3]);
                }else{
                    
                }    
        }
        if($limit<>NULL){
           return array_slice($butangan,-($limit)); 
        }else{
            return $butangan; 
        }  
         
    }
    
    function prefix_add($number,$type=NULL){
        switch($number){
            case '1':
                if(isset($type)){
                    return "st";    
                }else{
                    return "First";
                }
                break;
                
            case '2':
                if(isset($type)){
                    return "nd";    
                }else{
                    return "Second";
                }
                break;  
            
             case '3':
                 if(isset($type)){
                    return "rd";    
                }else{
                    return "Third";
                }
                break;     
                
             case '4':
                 if(isset($type)){
                    return "th";    
                }else{
                    return "Fourth";
                }
                break;   
                
             case '5':
                 if(isset($type)){
                    return "th";    
                }else{
                    return "Fifth";
                }
                break;                  
        }
    }
    
    
 } 
?>
